﻿using OneSoft.DataAccess;
using OneSoft.EntityCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace BySoft.Server.DataAccess.UserCode
{
    public partial class BsMatUseDataAccess
    {
        public static DataTable SelectDialogScheduleDetailList(string strWorkOrderNo, string strScheduleID, string strProductID, string strProductName, int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                #region SQL Server
                string strcmd = @"select sd.*,od.ProductID,p.ProductName 
                                from BS_ScheduleDetail sd
                                left join BS_OrderDetail od on od.WorkOrderNo=sd.WorkOrderNo
                                left join MD_Product p on p.ProductID=od.ProductID
                                where 1=1 ";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                if (!string.IsNullOrEmpty(strWorkOrderNo))
                {
                    strcmd += " and sd.WorkOrderNo LIKE @strWorkOrderNo";
                    dpc.AddWithValue("@strWorkOrderNo", "%" + strWorkOrderNo + "%");
                }
                if (!string.IsNullOrEmpty(strScheduleID))
                {
                    strcmd += " and sd.ScheduleID LIKE @strScheduleID";
                    dpc.AddWithValue("@strScheduleID", "%" + strScheduleID + "%");
                }
                if (!string.IsNullOrEmpty(strProductID))
                {
                    strcmd += " and od.ProductID LIKE @strProductID";
                    dpc.AddWithValue("@strProductID", "%" + strProductID + "%");
                }
                if (!string.IsNullOrEmpty(strProductName))
                {
                    strcmd += " and p.ProductName LIKE @strProductName";
                    dpc.AddWithValue("@strProductName", "%" + strProductName + "%");
                }
                strcmd += " ORDER BY sd.ScheduleID DESC,sd.ScheduleDetailID asc";

                return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
                #endregion
            }
            catch
            {
                throw;
            }
        }

        public static DataTable SelectMaterial(string strMaterialID, string strMaterialName, string ScheduleDetailID, string BillID, int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                #region SQL Server
                string strcmd = @"select sm.ScheduleMatID,sm.ScheduleDetailID,sm.BOMDetailID,sm.MaterialID,
		                                    sm.TotalNum as NeedNums,sm.TotalWeight,m.MaterialName,m.Specifications,
		                                    m.Unit,m.MMNumber as SingleMMNumber,v.ThisNum as StockNums,
		                                    bd.ChangeNum,c.RecNums,ISNULL(sm.TotalNum,0)-ISNULL(c.RecNums,0) as LeftNums
                                    from BS_ScheduleMat sm
                                    left join MD_Material m on m.MaterialID=sm.MaterialID
                                    left join V_MaterialStockNum v on v.ID=m.MaterialID
                                    left join BS_BOMDetail bd on bd.BOMDetailID=sm.BOMDetailID
                                    left join (select ScheduleMatID,SUM(ISNULL(Nums,0)) as RecNums 
			                                    from  BS_MatUseDetail 
			                                    where BillID<>@BillID
			                                    group by ScheduleMatID) c on c.ScheduleMatID=sm.ScheduleMatID
                                    where ISNULL(sm.TotalNum,0)-ISNULL(c.RecNums,0)>0 AND sm.ScheduleDetailID=@ScheduleDetailID";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                dpc.AddWithValue("@ScheduleDetailID", ScheduleDetailID);
                dpc.AddWithValue("@BillID", BillID);
                if (!string.IsNullOrEmpty(strMaterialID))
                {
                    strcmd += " and sm.MaterialID LIKE @strMaterialID";
                    dpc.AddWithValue("@strMaterialID", "%" + strMaterialID + "%");
                }
                if (!string.IsNullOrEmpty(strMaterialName))
                {
                    strcmd += " and m.MaterialName LIKE @strMaterialName";
                    dpc.AddWithValue("@strMaterialName", "%" + strMaterialName + "%");
                }
                strcmd += " ORDER BY sm.ScheduleMatID DESC";

                return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
                #endregion
            }
            catch
            {
                throw;
            }
        }

        public static DataTable SelectList(string strBillID, string strFrom, string strTo, string strWorkOrderNo, string strMaterialName, int IMatUseCount, out int icurPageIndex, int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                #region SQL Server
                string strcmd = @"select A.* from V_MatUseDetail A WHERE 1=1 ";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                if (!string.IsNullOrEmpty(strBillID))
                {
                    strcmd += " and A.BillID LIKE @strBillID";
                    dpc.AddWithValue("@strBillID", "%" + strBillID + "%");
                }

                if (!string.IsNullOrEmpty(strFrom))
                {
                    strcmd += " and A.BillDate >= @strFrom";
                    dpc.AddWithValue("@strFrom", strFrom);
                }
                if (!string.IsNullOrEmpty(strTo))
                {
                    strcmd += " and A.BillDate <= @strTo";
                    dpc.AddWithValue("@strTo", strTo);
                }

                if (!string.IsNullOrEmpty(strWorkOrderNo))
                {
                    strcmd += " and A.WorkOrderNo LIKE @WorkOrderNo";
                    dpc.AddWithValue("@WorkOrderNo", "%" + strWorkOrderNo + "%");
                }

                if (!string.IsNullOrEmpty(strMaterialName))
                {
                    strcmd += " and A.MaterialName LIKE @MaterialName";
                    dpc.AddWithValue("@MaterialName", "%" + strMaterialName + "%");
                }


                strcmd += " ORDER BY A.BillID DESC";

                return DataAccessCommon.ExecuteDataTablePagingExt(strcmd, out icurPageIndex, pageIndex, PageSize, dpc, IMatUseCount, out m_count);
                #endregion
            }
            catch
            {
                throw;
            }
        }

        public static DataTable GetDataByID(string id)
        {
            try
            {
                #region SQL Server
                string strcmd = @"select mu.*,mud.BillDetailID,mud.ScheduleMatID,mud.MaterialID,mud.Nums,mud.MMNumber,mud.Remark,
		                                p.ParameterName as MatUseTypeName,m.MaterialName,m.Specifications,m.Unit,
		                                m.MMNumber as SingleMMNumber,bd.ChangeNum,mud.Nums,v.ThisNum as StockNums,
		                                sd.ScheduleNum,sd.ScheduleID,pr.ProductID,pr.ProductName,sm.TotalNum as NeedNums,
		                                c.RecNums,ISNULL(sm.TotalNum,0)-ISNULL(c.RecNums,0) as LeftNums
                                from BS_MatUse mu
                                left join BS_MatUseDetail mud on mud.BillID=mu.BillID
                                left join MD_Material m on m.MaterialID=mud.MaterialID
                                left join BS_ScheduleMat sm on sm.ScheduleMatID=mud.ScheduleMatID
                                left join BS_BOMDetail bd on bd.BOMDetailID=sm.BOMDetailID
                                left join BS_ScheduleDetail sd on sd.ScheduleDetailID=mu.ScheduleDetailID
                                left join BS_OrderDetail od on od.WorkOrderNo=sd.WorkOrderNo
                                left join MD_Product pr on pr.ProductID=od.ProductID
                                left join SYS_Parameter p on p.ParameterCode=mu.MatUseType and p.ParameterTypeID='MatUseType'
                                left join V_MaterialStockNum v on v.ID=mud.MaterialID
                                left join (select ScheduleMatID,SUM(ISNULL(Nums,0)) as RecNums 
			                                from  BS_MatUseDetail 
			                                where BillID<>@strBillID 
			                                group by ScheduleMatID) c on c.ScheduleMatID=sm.ScheduleMatID
                                where mu.BillID=@strBillID";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                dpc.AddWithValue("@strBillID", id);

                return DataAccessCommon.Select(strcmd, dpc).Tables[0]; ;
                #endregion
            }
            catch
            {
                throw;
            }
        }
    }
}
